import 'package:book_app/product/models/device.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class DevicesDatabase {
  static final DevicesDatabase instance = DevicesDatabase._init();

  static Database? _database;

  DevicesDatabase._init();

  Future<Database> get database async {
    if (_database != null) return _database!;

    _database = await _initDB('devices.db');
    return _database!;
  }

  Future<Database> _initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);

    return await openDatabase(path, version: 1, onCreate: _createDB);
  }

  Future _createDB(Database db, int version) async {
    const idType = 'INTEGER PRIMARY KEY AUTOINCREMENT';
    const textType = 'TEXT NOT NULL';
    const boolType = 'BOOLEAN NOT NULL';
    const integerType = 'INTEGER NOT NULL';

    await db.execute('''
CREATE TABLE $tableDevices ( 
  ${DeviceFields.id} $idType, 
  ${DeviceFields.deviceId} $textType,
  ${DeviceFields.name} $textType,
  ${DeviceFields.nickName} $textType,
  ${DeviceFields.mac} $textType,
  ${DeviceFields.mcuVersion} $textType,
  ${DeviceFields.bleVersion} $textType, 
  ${DeviceFields.createTime} $textType,
  ${DeviceFields.placeId} $textType,
  ${DeviceFields.floorId} $textType,
  ${DeviceFields.roomId} $textType,
  ${DeviceFields.productId} $integerType,
  ${DeviceFields.catId} $integerType,
  ${DeviceFields.productKey} $integerType,
  ${DeviceFields.isOffline} $boolType,
  ${DeviceFields.serialNumber} $textType,
  ${DeviceFields.clientId} $textType,
  ${DeviceFields.host} $textType,
  ${DeviceFields.deviceAccessKeyId} $textType,
  ${DeviceFields.deviceAccessKeySecret} $textType,
  ${DeviceFields.instanceId} $textType,
  ${DeviceFields.ip} $textType,          
  ${DeviceFields.subnetMask} $textType,        
  ${DeviceFields.gatewayAddress} $textType,        
  ${DeviceFields.dhcp} $textType,                                                                                         ${DeviceFields.instanceId} $textType,
  )
''');
  }

  Future<Device> create(Device device) async {
    final db = await instance.database;

    // final json = note.toJson();
    // final columns =
    //     '${NoteFields.title}, ${NoteFields.description}, ${NoteFields.time}';
    // final values =
    //     '${json[NoteFields.title]}, ${json[NoteFields.description]}, ${json[NoteFields.time]}';
    // final id = await db
    //     .rawInsert('INSERT INTO table_name ($columns) VALUES ($values)');

    final id = await db.insert(tableDevices, device.toJson());
    return device.copy(id: id);
  }

  Future<Device> readDevice(int id) async {
    final db = await instance.database;

    final maps = await db.query(
      tableDevices,
      columns: DeviceFields.values,
      where: '${DeviceFields.id} = ?',
      whereArgs: [id],
    );

    if (maps.isNotEmpty) {
      return Device.fromJson(maps.first);
    } else {
      throw Exception('ID $id not found');
    }
  }

  Future<List<Device>> readAllDevices() async {
    final db = await instance.database;

    const orderBy = '${DeviceFields.createTime} ASC';
    // final result =
    //     await db.rawQuery('SELECT * FROM $tableNotes ORDER BY $orderBy');

    final result = await db.query(tableDevices, orderBy: orderBy);

    return result.map((json) => Device.fromJson(json)).toList();
  }

  Future<int> update(Device device) async {
    final db = await instance.database;

    return db.update(
      tableDevices,
      device.toJson(),
      where: '${DeviceFields.id} = ?',
      whereArgs: [device.id],
    );
  }

  Future<int> delete(int id) async {
    final db = await instance.database;

    return await db.delete(
      tableDevices,
      where: '${DeviceFields.id} = ?',
      whereArgs: [id],
    );
  }

  Future close() async {
    final db = await instance.database;

    db.close();
  }
}
